const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'Lycc.2025',
    database: 'fun_english_db',
    charset: 'utf8mb4'
};

// 年级映射
const gradeMap = {
    '三上': '三上',
    '三下': '三下',
    '四上': '四上',
    '四下': '四下',
    '五上': '五上',
    '五下': '五下',
    '六上': '六上',
    '六下': '六下'
};

// 解析Markdown文件
function parseVocabularyFile(filePath) {
    const content = fs.readFileSync(filePath, 'utf-8');
    const lines = content.split('\n');
    const vocabulary = [];
    
    for (let line of lines) {
        // 匹配格式：序号 课本 主题 单词 音标 中文
        // 例如：1 三上 Unit 1 ruler /'ruːlə/ 尺子
        const match = line.match(/^(\d+)\s+(三上|三下|四上|四下|五上|五下|六上|六下)\s+(Unit\s+\d+)\s+([^\s]+)\s+\/([^\/]+)\/\s+(.+)$/);
        
        if (match) {
            const [, id, grade, unit, word, phonetic, chinese] = match;
            vocabulary.push({
                id: parseInt(id),
                grade: gradeMap[grade],
                unit: unit.trim(),
                word: word.trim(),
                phonetic: `/${phonetic.trim()}/`,
                chinese: chinese.trim()
            });
        }
    }
    
    return vocabulary;
}

// 导入数据到数据库
async function importVocabulary() {
    let connection;
    
    try {
        console.log('连接数据库...');
        connection = await mysql.createConnection(dbConfig);
        
        console.log('读取词汇文件...');
        const vocabFilePath = path.join(__dirname, '../docs/tomarkdown.org-2025-10-09T07-31-18.md');
        const vocabulary = parseVocabularyFile(vocabFilePath);
        
        console.log(`成功解析 ${vocabulary.length} 个单词`);
        
        // 清空现有数据
        console.log('清空现有词汇数据...');
        await connection.execute('DELETE FROM vocabulary');
        
        // 批量插入
        console.log('开始导入数据...');
        const insertQuery = `
            INSERT INTO vocabulary (word, phonetic, chinese, grade, unit)
            VALUES (?, ?, ?, ?, ?)
        `;
        
        let successCount = 0;
        let errorCount = 0;
        
        for (let item of vocabulary) {
            try {
                await connection.execute(insertQuery, [
                    item.word,
                    item.phonetic,
                    item.chinese,
                    item.grade,
                    item.unit
                ]);
                successCount++;
                
                if (successCount % 100 === 0) {
                    console.log(`已导入 ${successCount} 个单词...`);
                }
            } catch (error) {
                console.error(`导入单词失败: ${item.word}`, error.message);
                errorCount++;
            }
        }
        
        console.log('\n=== 导入完成 ===');
        console.log(`成功: ${successCount} 个`);
        console.log(`失败: ${errorCount} 个`);
        
        // 显示统计信息
        const [stats] = await connection.execute(`
            SELECT grade, COUNT(*) as count 
            FROM vocabulary 
            GROUP BY grade 
            ORDER BY FIELD(grade, '三上', '三下', '四上', '四下', '五上', '五下', '六上', '六下')
        `);
        
        console.log('\n=== 各年级单词数量 ===');
        stats.forEach(row => {
            console.log(`${row.grade}: ${row.count} 个单词`);
        });
        
    } catch (error) {
        console.error('导入过程出错:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('\n数据库连接已关闭');
        }
    }
}

// 执行导入
if (require.main === module) {
    importVocabulary().catch(console.error);
}

module.exports = { parseVocabularyFile, importVocabulary };

